Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Zero-length character strings

In addition to accepting the unknown operator, ORACLE assumes that all zero-length character strings are unknown and stores them as NULL. In addition, a zero-length character string is represented as a single space in the ORACLE database. This allows OpenEdge applications to distinguish between the Unknown value (?) and zero-length character strings.

When you use the Unknown value (?) in a WHERE clause with the DataServer, the Unknown value (?) satisfies only the equals (=) operator. Both of the following statements find the first customer record with the Unknown value (?) in the address2 field. Notice the space between the quotation marks in the first statement:

FIND FIRST customer WHERE address2 = " ". FIND FIRST customer WHERE address2 = "".

Although "" and " " evaluate the same way in a WHERE clause, they have different results when you use them with the BEGINS function. For example, the following statement retrieves all customer names except those that have the Unknown value (?):

FOR EACH customer WHERE name BEGINS "":

The following statement uses " " to retrieve only those names that begin with a space:

FOR EACH customer WHERE name BEGINS " ":

Because Unknown value (?), or values, satisfy only the equals condition, the following code does not retrieve customers with an Unknown value (?) in the address2 field:

FOR EACH customer WHERE address2 <> "foo": DISPLAY name. END.

The following statement is not meaningful to ORACLE. It generates the error, “Illegal operator for Unknown value (?) or zero-length character string”:

FIND FIRST customer WHERE address2 < ?.

This restriction has been relaxed for columns of the DATE data type, as shown in the following statement:

FIND FIRST order WHERE orderdate < ?.